﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Collections;
using System.Windows.Forms;
using System.Drawing.Printing;
using System.Data.SqlClient;
//using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;


namespace LOGIN
{

    public partial class InventoryProcessing : Form
    {
        private DataTable ITable;
        public InventoryProcessing()
        {
            InitializeComponent();
            this.dgv_inventory.AllowUserToAddRows = false;
            this.dgv_inventory.BackgroundColor = Color.White;                                                   
            this.dgv_inventory.AutoSizeColumnsMode =
                DataGridViewAutoSizeColumnsMode.AllCells;
            //this.dgv_inventory.Font.Size = s;
        }
        private DataTable InventoryTable;



        private void InventoryProcessing_Load(object sender, EventArgs e)
        {
            name.Text = ordersHelper.UserName;
            label3.Text = DateTime.Now.ToLongDateString().ToString();
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
                "Server=(local);Database=MyHospital;Integrated Security=sspi";
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = "SELECT MedicineID,DrugName,MedicineSum,MinNum,dbo.fn_GetPy(DrugName) AS pymc FROM dbo.medicineData;";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            this.InventoryTable = new DataTable();
            this.ITable = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter.Fill(ITable);
            sqlDataAdapter.Fill(InventoryTable);
            this.ITable.TableName = "tablename";
            sqlConnection.Close();
            this.dgv_inventory.DataSource = InventoryTable;
            dgv_inventory.Columns["MedicineID"].HeaderText = "ID";
            dgv_inventory.Columns["DrugName"].HeaderText = "药品名";
            dgv_inventory.Columns["MedicineSum"].HeaderText = "库存量";
            dgv_inventory.Columns["MinNum"].HeaderText = "警告库存";
            dgv_inventory.Columns["pymc"].Visible = false;
            dgv_inventory.ReadOnly = true;
            PZS.Text = dgv_inventory.RowCount.ToString();
            int m = 0;

            for (m = 0; m < Convert.ToInt32(PZS.Text.ToString()); m++)
            {
                if (Convert.ToInt32(dgv_inventory.Rows[m].Cells["MinNum"].Value.ToString()) - Convert.ToInt32(dgv_inventory.Rows[m].Cells["MedicineSum"].Value.ToString()) > 0)
                {
                    dgv_inventory.Rows[m].DefaultCellStyle.ForeColor = Color.Red;
                }
            }


        }

        private void btn_print_Click(object sender, EventArgs e)
        {
            
            string fileName = "";

            string saveFileName = "";

            SaveFileDialog saveDialog = new SaveFileDialog();

            saveDialog.DefaultExt = "xls";

            saveDialog.Filter = "Excel文件|*.xls";

            saveDialog.FileName = fileName;

            saveDialog.ShowDialog();

            saveFileName = saveDialog.FileName;

            if (saveFileName.IndexOf(":") < 0) return; 

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)
            {

                MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");

                return;

            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

            //标题             

            for (int i = 0; i < dgv_inventory.ColumnCount; i++)

            { worksheet.Cells[1, i + 1] = dgv_inventory.Columns[i].HeaderText; }

            //数值

            for (int r = 0; r < dgv_inventory.Rows.Count; r++)
            {
                for (int i = 0; i < dgv_inventory.ColumnCount; i++)
                {

                    worksheet.Cells[r + 2, i + 1] = dgv_inventory.Rows[r].Cells[i].Value;
                    

                }

                System.Windows.Forms.Application.DoEvents();

            }

            worksheet.Columns.EntireColumn.AutoFit();
            

            MessageBox.Show(fileName + "信息导出成功", "提示", MessageBoxButtons.OK);

            if (saveFileName != "")
            {

                try
                {
                    workbook.Saved = true;

                    workbook.SaveCopyAs(saveFileName);  //fileSaved = true;                 

                }

                catch (Exception ex)
                {                  

                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);

                }

            }

            xlApp.Quit();

            GC.Collect();       

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            DataRow[] searchResultRows =
                this.InventoryTable.Select("pymc LIKE '%" + this.textBox1.Text.Trim() + "%'");           
            DataTable searchResultTable = this.InventoryTable.Clone();                                         
            foreach (DataRow row in searchResultRows)                                                       
            {
                searchResultTable.ImportRow(row);                                                           
            }
            this.dgv_inventory.DataSource = searchResultTable;  
        }

        private void button2_Click(object sender, EventArgs e)
        {
            InventoryProcessing f = new InventoryProcessing();
            f.Close();

        }
    }
}
        